﻿// -----------------------------------------------------------------------
// <copyright file="DbAccess.cs" company="JSC Centido">
//  Copyright (c) JSC Centido. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

using System.Data.Odbc;

namespace Centido.Core
{
    using System;
    using System.Data;
    using System.Data.Common;


    /// <summary>
    ///   A database access helper used to ease the execution of ADO.NET commands.
    /// </summary>
    /// <remarks>
    ///   The class contains a set of methods that can be used when configuring and
    ///   executing ADO.NET commands.
    /// </remarks>
    public sealed class DbAccess
    {
        /// <summary>
        ///   The database connection string.
        /// </summary>
        private readonly string _dbConnectionString;

        /// <summary>
        ///   The database connection.
        /// </summary>
        private OdbcConnection _dbConnection;


        /// <summary>
        ///   Initializes a new instance of the <see cref = "Centido.Core.Domain" /> class
        ///   using the specified database connection string and the database provider invariant name.
        /// </summary>
        /// <param name = "dbConnectionString">The database connection string.</param>
        /// <exception cref = "System.ArgumentNullException">Thrown when the value of <paramref name = "dbConnectionString" /> is null.</exception>
        /// <exception cref = "System.ArgumentException">Thrown when the value of <paramref name = "dbConnectionString" /> is an empty string.</exception>
        public DbAccess(string dbConnectionString)
        {
            if (dbConnectionString == null)
                throw new ArgumentNullException("dbConnectionString");
            if (dbConnectionString.Trim().Length == 0)
                throw new ArgumentException("Connection string cannot be empty.");

            _dbConnectionString = dbConnectionString.Trim();
        }


        /// <summary>
        ///   Gets an instance of the <see cref = "System.Data.Common.DbConnection" /> class
        ///   used to connect to the database.
        /// </summary>
        /// <value>An instance of the <see cref = "System.Data.Common.DbConnection" /> class.</value>
        public OdbcConnection DbConnection
        {
            get { return _dbConnection ?? (_dbConnection = new OdbcConnection(_dbConnectionString)); }
        }


        /// <summary>
        ///   Opens the connection to the database.
        /// </summary>
        /// <returns>Returns an instance of the <see cref = "System.Data.Common.DbConnection" />
        ///   used to connect to the database.</returns>
        /// <exception cref = "System.Data.Common.DbException">Thrown when establishing a connection to the database failed.</exception>
        public DbConnection OpenDbConnection()
        {
            if (DbConnection.State != ConnectionState.Open)
                DbConnection.Open();

            return DbConnection;
        }


        /// <summary>
        ///   Closes the database connection.
        /// </summary>
        public void CloseDbConnection()
        {
            DbConnection.Close();
        }


        /// <summary>
        ///   Creates a specific to the current database ADO.NET command.
        /// </summary>
        /// <returns>Returns the created ADO.NET command.</returns>
        public OdbcCommand CreateCommand()
        {
            var cmd = new OdbcCommand 
            {
                Connection = DbConnection,
                CommandTimeout = 0
            };

            // The command is allowed to execute indefinitely

            return cmd;
        }


        /// <summary>
        ///   Creates an ADO.NET command parameter.
        /// </summary>
        /// <param name = "parameterName">The ADO.NET parameter name.</param>
        /// <param name = "dbType">The ADO.NET parameter database type.</param>
        /// <param name = "value">The ADO.NET parameter value.</param>
        /// <returns>Returns the created ADO.NET command parameter.</returns>
        /// <exception cref = "System.ArgumentNullException">Thrown when the value of <paramref name = "parameterName" /> is null.</exception>
        /// <exception cref = "System.ArgumentException">Thrown when the value of <paramref name = "parameterName" /> is an empty string.</exception>
        public OdbcParameter CreateParameter(string parameterName, OdbcType dbType, object value)
        {
            if (parameterName == null)
                throw new ArgumentNullException("parameterName");
            if (parameterName.Trim().Length == 0)
                throw new ArgumentException("Parameter name cannot be empty.");

            parameterName = parameterName.Trim();

            var parameter = new OdbcParameter
            {
                ParameterName = parameterName,
                OdbcType = dbType,
                Value = value,
                Direction = ParameterDirection.Input
            };

            return parameter;
        }


        /// <summary>
        ///   Executes an ADO.NET command that returns a scalar result.
        /// </summary>
        /// <param name = "configureDbCommand">The action of configuring the ADO.NET command.</param>
        /// <param name = "handleResult">The action of handling database command result.</param>
        /// <exception cref = "System.ArgumentNullException">Thrown when the value of <paramref name = "configureDbCommand" /> is null.</exception>
        /// <example    >
        ///   The following example shows how to execute an ADO.NET command that returns a scalar result.
        ///   <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class ExecuteScalerSample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///         
        ///             //Adding a new table
        ///             var table = domain.AddTable("my_table");
        ///         
        ///             //Granting all necessary permissions
        ///             table.GrantAll();
        /// 
        ///             //Inserting two rows
        ///             table.InsertRow();
        ///             table.InsertRow();
        ///         
        ///             //Reading the row count
        ///             int rowCount = 0;
        ///         
        ///             domain.DbAccess.ExecuteScalar(cmd =>
        ///             {
        ///                 cmd.CommandText = string.Format("select count(*) from {0}",
        ///                     table.FullName);
        ///             }, result => rowCount = DbValue.GetInteger(result).Value);
        ///         
        ///             Console.WriteLine("Row count = {0}", rowCount);
        ///         }
        ///     }
        ///   </code>
        /// </example>
        public void ExecuteScalar(Action<DbCommand> configureDbCommand, Action<object> handleResult)
        {
            if (configureDbCommand == null)
                throw new ArgumentNullException("configureDbCommand");

            try
            {
                using (var cmd = CreateCommand())
                {
                    configureDbCommand(cmd);

                    OpenDbConnection();

                    var result = cmd.ExecuteScalar();

                    if (handleResult != null)
                        handleResult(result);
                }
            }
            finally
            {
                CloseDbConnection();
            }
        }

        /// <summary>
        ///   Executes an ADO.NET command that returns multiple rows.
        /// </summary>
        /// <param name = "configureDbCommand">The action of configuring the ADO.NET command.</param>
        /// <param name = "handleResult">The action of handling an ADO.NET command results.</param>
        /// <exception cref = "System.ArgumentNullException">Thrown when the value of <paramref name = "configureDbCommand" /> is null.</exception>
        /// <example>
        ///   The following example shows how to execute an ADO.NET command that returns multiple rows.
        ///   <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class ExecuteReaderSample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///         
        ///             //Adding a new table
        ///             var table = domain.AddTable("my_table");
        ///         
        ///             //Adding a column
        ///             var column = table.AddColumn("my_column", ColumnType.Integer);
        ///         
        ///             //Granting all necessary permissions
        ///             table.GrantAll();
        /// 
        ///             //Inserting two rows
        ///             table.InsertRow(column.Value(1));
        ///             table.InsertRow(column.Value(2));
        ///         
        ///             var columnName = column.Name;
        ///         
        ///             //Reading inserted values
        ///             domain.DbAccess.ExecuteReader(cmd =>
        ///             {
        ///                 cmd.CommandText = string.Format("select id, {0} from {1}",
        ///                     column.Name,
        ///                     table.FullName);
        ///             }, reader =>
        ///             {
        ///                 var rowId = DbValue.GetGuid(reader["id"]).Value;
        ///                 var columnValue = DbValue.GetInteger(reader[columnName]).Value;
        /// 
        ///                 Console.WriteLine("Row id = {0}, column value = {1}", rowId, columnValue);
        ///             });         
        ///         }
        ///     }
        ///   </code>
        /// </example>
        public void ExecuteReader(Action<DbCommand> configureDbCommand, Action<DbDataReader> handleResult)
        {
            if (configureDbCommand == null)
                throw new ArgumentNullException("configureDbCommand");

            try
            {
                using (var cmd = CreateCommand())
                {
                    configureDbCommand(cmd);

                    OpenDbConnection();

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (handleResult != null)
                                handleResult(reader);
                        }
                    }
                }
            }
            finally
            {
                CloseDbConnection();
            }
        }


        /// <summary>
        ///   Executes an ADO.NET command that returns no result.
        /// </summary>
        /// <param name = "configureDbCommand">The action of configuring the ADO.NET command.</param>
        /// <returns>Returns the number of affected rows.</returns>
        /// <exception cref = "System.ArgumentNullException">Thrown when the value of <paramref name = "configureDbCommand" /> is null.</exception>
        /// <example>
        ///   The following example shows how to execute an ADO.NET command that returns no result.
        ///   <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class ExecuteNonQuerySample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///         
        ///             //Adding a new table
        ///             var table = domain.AddTable("my_table");
        ///         
        ///             //Granting all necessary permissions
        ///             table.GrantAll();
        /// 
        ///             //Inserting two rows
        ///             table.InsertRow();
        ///             table.InsertRow();
        ///         
        ///             //Deleting inserted rows
        ///             var rowCount = domain.DbAccess.ExecuteNonQuery(cmd =>
        ///             {
        ///                 cmd.CommandText = string.Format("delete from {0}",
        ///                 table.FullName);
        ///             });
        ///             
        ///             Console.WriteLine("Affected row count = {0}", rowCount);
        ///         }
        ///     }
        ///   </code>
        /// </example>
        public int ExecuteNonQuery(Action<DbCommand> configureDbCommand)
        {
            if (configureDbCommand == null)
                throw new ArgumentNullException("configureDbCommand");

            int rowCount;

            try
            {
                using (var cmd = CreateCommand())
                {
                    configureDbCommand(cmd);

                    OpenDbConnection();

                    rowCount = cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                CloseDbConnection();
            }

            return rowCount;
        }
    }
}